Project 1: Sorting the data
The following code was used to seperate the health indicators into seperate worksheets. The code was tested and refined on a smaller copy of the data.
Sub SeperateWorksheetCreator()
Dim ConditionName As String
Dim RowCount As Integer
Dim lastrow As Long
Dim I As Integer
Dim k As Integer
Create variable with called conditionnumber which will hold the Condition Code. Found in column A
ConditionName = Worksheets("health-indicators-for-china-1").Range("A" & 3).Value
Create New Sheet with name of first condition
Sheets.Add.Name = ConditionName
Determine the number of rows present
RowCount = Worksheets("health-indicators-for-china-1").UsedRange.Rows.Count
Start count from 3 as 1 and 2 are headings
For I = 3 To RowCount
For 1st entry copy the two headings
If I = 3 Then
Loop to do both headings. It would be more efficent to select two rows.
For k = 1 To 3
Sheets("health-indicators-for-china-1").Cells(k, "A").EntireRow.Copy
Sheets(ConditionName).Cells(k, "A").EntireRow.Insert
Next k
Goto used to Avoid repeating 1st Entry
GoTo AvoidRepeat
End If
Check code of cell in new sheet with next entry
If Sheets("health-indicators-for-china-1").Cells(I, "A").Value = Sheets(ConditionName).Cells(3, "A").Value Then
If true then copy the entire row
Sheets("health-indicators-for-china-1").Cells(I, "A").EntireRow.Copy
lastrow used to identify next blank row
lastrow = Sheets(ConditionName).Range("A65536").End(xlUp).Row
Paste the row on the last row
Sheets(ConditionName).Range("A" & lastrow).Insert
Else
ConditionName is reassigned to the next value. (Assumes Values in column are sorted)
ConditionName = Worksheets("health-indicators-for-china-1").Range("A" & I).Value
Create new sheet with condition code
Sheets.Add.Name = ConditionName
Paste headings
For k = 1 To 2
Sheets("health-indicators-for-china-1").Cells(k, "A").EntireRow.Copy
Sheets(ConditionName).Cells(k, "A").EntireRow.Insert
Next k
Copy the entry and paste to the next blank line
Sheets("health-indicators-for-china-1").Cells(I, "A").EntireRow.Copy
lastrow = Sheets(ConditionName).Range("A65536").End(xlUp).Row + 1
Sheets(ConditionName).Range("A" & lastrow).Insert
End If
AvoidRepeat:
Next I
End Sub
Worksheets were created for all unique health indicators, resulting in more than 100 worksheets. Some indicators only had a single entry, therefore, a seperate script was written to copy the data from any sheets with only one entry to a seperate worksheet. This was carried out using the following code.
Sub FindSingleRecords()
Dim WS_Count As Integer
Dim I As Integer
Dim Length As Integer
'Set WS_Count equal to the number of worksheets in the activeworkbook.
WS_Count = ActiveWorkbook.Worksheets.Count
' Begin the loop.
For I = 1 To WS_Count
Length = ActiveWorkbook.Worksheets(I).UsedRange.Rows.Count
If Length = 3 Then
'Copy the single entry
ActiveWorkbook.Worksheets(I).Cells(3, "A").EntireRow.Copy
'lastrow used to identify next blank row
lastrow = ActiveWorkbook.Worksheets(I).Range("A65536").End(xlUp).Row
'Paste the row on the last row of worksheet named "other".
Sheets("Other").Range("A" & lastrow).Insert
End If
Next I
End Sub
The worksheet had produced a list of IDs for the health indicators with one entry. This was list was then used to manually delete the worksheets with only one entry. This was carried out manually since it is impossible to undo deleting a worksheet in Excel. These indicators were then seperated according to the type of measurement (percentage, per 1000, per 10,000 and number), since this would allow for better graphical representation.
The other worksheets were then first sorted by sex and then by year to enable graphical representation.